<?php
namespace console\controllers;

use common\models\Code;
use common\models\Phone;
use common\service\ExcelService;
use common\service\SmsService;
use PhpOffice\PhpSpreadsheet\IOFactory;
use yii\console\Controller;
use yii\db\Query;

class QueueController extends Controller
{
    public function actionIndex(){
//        $sql = "delete from phone_02 where phone >= '17000000000';";
//            $row = Phone::getDb()->createCommand($sql)
//                ->query()->read();
//            var_dump($row);exit;
        print_r(1);exit;

    }


    public function actionTest(){
        set_time_limit(0);
        //这个地方号段可能不全,后期发现缺少的在增加
//        $arr = [
//            130,131,132,133,134,135,136,137,138,139,
//            144,145,147,
//            150,151,152,153,155,156,157,158,159,
//            170,173,175,176,177,178,
//            180,181,182,183,184,185,186,187,188,189,
//            199
//        ];
        $arr = [
            170,173,175
        ];

        $str = '00000000';

        while($item = array_shift($arr))
        {
            $item .= $str;
            for($i = 0;$i <= 999;$i++)
            {
                $data = [];
                for($j = 0;$j < 100000;$j++)
                {
                    $phone  = $item + (($i * 100000) + $j);
                    $phone_md5 = md5($phone);
                    $key = (string)$phone_md5[0] . (string)$phone_md5[1];

                    if(!array_key_exists($key,$data)){
                        $data[$key] = [];
                    }
                    array_push($data[$key],[
                        'phone'=>$phone,
                        'phone_md5'=>$phone_md5
                    ]);
                }

                foreach($data as $key=>$phones)
                {
                    $table_name = 'phone_' . $key;
//                     $ret = Phone::getDb()->createCommand("SHOW TABLES LIKE '{$table_name}'")->queryAll();
//                     if(count($ret) == 0){
//                         $sql = <<<EOF
// CREATE TABLE $table_name (
//   `id` int(11) NOT NULL AUTO_INCREMENT,
//   `phone` char(11) NOT NULL,
//   `phone_md5` char(32) NOT NULL,
//   PRIMARY KEY (`id`)
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8
// EOF;

//                         Phone::getDb()->createCommand($sql)->execute();
//                     }

                    Phone::getDb()->createCommand()
                        ->batchInsert($table_name,['phone','phone_md5'],$phones)
                        ->execute();
                }
                $data = [];
            }
        }
        //  插入成功
        echo '插入成功';
    }


    public function actionImport()
    {
        //xlsx 格式
//        $reader = IOFactory::createReader('Xlsx');
        //csv 格式
        for($i=200000;$i<4100000;$i = $i+10000){

        $reader = IOFactory::createReader('Csv');
        $spreat_sheet = $reader->load('/var/www/phone_md5/newjidai/data_'.$i.'.csv');
        $data = $spreat_sheet->getActiveSheet()->toArray();
//        $data = mime_content_type('/var/www/phone_md5/newjidai/data.csv');

        //2.0版本
        $new_data = [];
        foreach($data as $key=>$value){
            if($key == 0){
                continue;
            }
            $value = ltrim($value[0]);
            $table_name = substr($value,0,2);
            $table_name = 'phone_' .$table_name;

            if(!array_key_exists($table_name,$new_data)){
                $new_data[$table_name] = [];
            }
            $new_data[$table_name][]= $value;
        }
        $new_array = [];
        foreach ($new_data as $key=>$val){
            $table_name = $key;
            $phones = (new Query())
                ->from($table_name)
                ->where(['phone_md5'=>$val])
                ->all();
            if($phones){
                $new_array = array_merge($new_array,$phones);
            }

        }
            //1.0版本
//        $new_array = [];
//        foreach ($data as $key=>$value)
//        {
//            $value = $value[0];
//            $table_name = $value[0] . $value[1];
//            $sql = "select * from phone_". $table_name ." where phone_md5 = '$value'";
//            $row = Phone::getDb()->createCommand($sql)
//                ->query()->read();
//            if(!$row){
//                $row = [
//                    'id' => '',
//                    'phone'=>'',
//                    'phone_md5'=>$value
//                ];
//            }
//            array_push($new_array,$row);
//        }


        // 保存数据. xlsx格式
//        ExcelService::exportExcel($new_array,'/var/www/phone_md5/data.xlsx',true);
        // 保存数据 csv格式
        ExcelService::exportExcel($new_array,'/var/www/phone_md5/new_data/data_'.$i.'.csv',true);
        }

    }



    public function actionNewAdd(){
        set_time_limit(0);
        $arr = [
        	173,175
        ];

        $str = '00000000';

        while($item = array_shift($arr))
        {
            $item .= $str;
            for($i = 0;$i <= 999;$i++)
            {
                $data = [];
                for($j = 0;$j < 100000;$j++)
                {
                    $phone  = $item + (($i * 100000) + $j);
                    $phone_md5 = md5($phone);
                    $key = (string)$phone_md5[0] . (string)$phone_md5[1];

                    if(!array_key_exists($key,$data)){
                        $data[$key] = [];
                    }
                    array_push($data[$key],[
                        'phone'=>$phone,
                        'phone_md5'=>$phone_md5
                    ]);
                }

                foreach($data as $key=>$phones)
                {
                    $table_name = 'phone_' . $key;
                    Phone::getDb()->createCommand()
                        ->batchInsert($table_name,['phone','phone_md5'],$phones)
                        ->execute();
                }
                $data = [];
            }
        }
        //  插入成功
        echo '插入成功';
    }

    //新增一个字段用来标记该号码是哪一个运营商
    public function actionModifyTable(){
        $a = ['1','2','3','4','5','6','7','8','9','a','b','c','d','e','f'];
        foreach ($a as $item){
            foreach ($a as $value){
                $table = 'phone_'.$item.$value;
                $sql = 'ALTER TABLE `'.$table.'` ADD `type` tinyint(1) NULL;';
                Phone::getDb()->createCommand($sql)->execute();
                echo $table.'操作成功\n';
            }
        }
        echo 'ok';

    }

    //标记改号码属于哪个运营商 1 移动 2 联通 3电信
    public function actionSignType(){
        $a = [139,138,137,136,135,159,158,157,150,151,152,188,187,182,183,184,178,147,198,148];//移动
        $a = implode($a,',');
        $b =[130,131,132,156,155,186,185,176,175,145,171,166,146];//联通
        $b = implode($b,',');

        $c = [133,153,189,180,181,177,173,149,199,];//电信
        $c = implode($c,',');



        $a1 = [1340,1341,1342,1343,1344,1345,1346,1347,1348,1703,1705,1706,1440];//移动
        $a1 = implode($a1,',');

        $b2 =[1707,1708,1709];//联通
        $b2 = implode($b2,',');

        $c3 = [1349,1700,1701,7102,1410,];//电信
        $c3 = implode($c3,',');

        /**
         * update 表名 set type = 1 where substring(phone,1,3) in ('130','131','132','133')
         */

        $t = ['1','2','3','4','5','6','7','8','9','a','b','c','d','e','f'];
        foreach ($t as $item){
            foreach ($t as $value){
                $table = 'phone_'.$item.$value;
                $sql1 = 'update `'.$table.'` set type = 1 where substring(phone,1,3) in ('.$a.');';
                Phone::getDb()->createCommand($sql1)->execute();

                $sql2 = 'update `'.$table.'` set type = 1 where substring(phone,1,4) in ('.$a1.');';
                Phone::getDb()->createCommand($sql2)->execute();

                $sql3 = 'update `'.$table.'` set type = 2 where substring(phone,1,3) in ('.$b.');';
                Phone::getDb()->createCommand($sql3)->execute();

                $sql4 = 'update `'.$table.'` set type = 2 where substring(phone,1,4) in ('.$b2.');';
                Phone::getDb()->createCommand($sql4)->execute();

                $sql5 = 'update `'.$table.'` set type = 3 where substring(phone,1,3) in ('.$c.');';
                Phone::getDb()->createCommand($sql5)->execute();

                $sql6 = 'update `'.$table.'` set type = 3 where substring(phone,1,4) in ('.$c3.');';
                Phone::getDb()->createCommand($sql6)->execute();
                echo $table.'操作成功\n';
            }
        }
        echo 'ok';
    }

    public function actionExport(){
        $move = [
            139,138,137,136,135,159,158,157,150,151,152,188,187,182,183,184,178,147,198,148,
            1340,1341,1342,1343,1344,1345,1346,1347,1348,1703,1705,1706,1440
        ];
        $move = [
            139,138,137,136,135,159,158,157,150,151,152,188,187,182,183,184,178,147,198,148,
            1340,1341,1342,1343,1344,1345,1346,1347,1348,1703,1705,1706,1440
        ];
//        $a = [139,138,137,136,135,159,158,157,150,151,152,188,187,182,183,184,178,147,198,148];//移动
//        $a1 = [1340,1341,1342,1343,1344,1345,1346,1347,1348,1703,1705,1706,1440];//移动

        $contact = [130,131,132,156,155,186,185,176,175,145,171,166,146,1707,1708,1709];
//        $b =[130,131,132,156,155,186,185,176,175,145,171,166,146];//联通
//        $b2 =[1707,1708,1709];//联通

        $dian = [
            133,153,189,180,181,177,173,149,199,1349,1700,1701,7102,1410,
        ];

        // type
        $arr = [
            1   =>  $move,
            2   =>  $contact,
            3   =>  $dian
        ];

        $type = 1;

        $data = $arr[$type];

        $diff = [];
        $choice = array_diff($data,array_intersect($diff,$data));
        shuffle($choice);
        // 可选的.
        $phone_prefix = array_shift($choice);

        $pad_string = str_pad(0,11 - strlen($phone_prefix),0);
        // 初始号码.
        $phone = $phone_prefix . $pad_string;
        $num = 1 . $pad_string;

        for($i = 0,$len = $num / 1000000;$i < $len; $i++)
        {
            $data = [];
            for($j = 0;$j < 1000000;$j++)
            {
                $mobile = $phone + $i * 1000000 + $j;
                array_push($data,[
                    'phone'=>$mobile,
                    'phone_md5'=>md5($mobile)
                ]);
                print_r($data);exit;
            }
            shuffle($data);
            $title = ['phone','phone_md5'];
            array_unshift($data,$title);
            $file_path = '/var/www/phone_md5/create/a/'.$phone_prefix . $i.'.csv';
            ExcelService::exportExcel($data,$file_path,true);
            $this->stdout($phone_prefix . $i ." success\n");
        }
        $this->stdout(" ok\n");

//        $c = [133,153,189,180,181,177,173,149,199,];//电信
//        $c3 = [1349,1700,1701,7102,1410,];//电信

//        $str = '00000000';
//        $arr = [1340,1341,1342,1343,1344,1345,1346,1347,1348,1703,1705,1706,1440,
//            1707,1708,1709,
//            1349,1700,1701,7102,1410,];


//
//        while($item = array_shift($arr))
//        {
//            $file = $item;
//            $item .= $str;
//            for($i = 0;$i <= 999;$i++)
//            {
//                $data = [];
//                for($j = 0;$j < 10000;$j++)
//                {
//                    $phone  = $item + (($i * 10000) + $j);
//                    $phone_md5 = md5($phone);
//
//                    array_push($data,[
//                        'phone'=>$phone,
//                        'phone_md5'=>$phone_md5
//                    ]);
//                }
//
//
//            }
////            print_r($data);exit;
//            $title = ['phone','phone_md5'];
//            array_unshift($data,$title);
//            $file_path = '/var/www/phone_md5/create/a/'.$file.'.csv';
//            ExcelService::exportExcel($data,$file_path,true);
//            echo $file.'ok';
//        }

    }


    //新增的导出文件的
    public function actionImportTxt()
    {

        $data = file('/var/www/phone_md5/jidai_1');

        $new_data = [];
        foreach($data as $key=>$value){
            $value = substr($value,0,32);
            $value = ltrim($value,'');
            $table_name = substr($value,0,2);
            $table_name = 'phone_' .$table_name;

            if(!array_key_exists($table_name,$new_data)){
                $new_data[$table_name] = [];
            }
            $new_data[$table_name][]['md5']= $value;
        }
        foreach ($new_data as $k=>$v){
//            $title = ['md5'];
//            array_unshift($v,$title);
            ExcelService::exportExcel($v,'/var/www/phone_md5/newjidai_1/data_'.$k.'.csv',true);
            $this->stdout( $k ." success\n");

        }
        exit;

//        $new_data = [];
//        $i = 0;
//        foreach ($data as $key=>$val){
//            $title = ['md5'];
//            if($i<10000){
//
//                array_push($new_data,[
//                    'md5' => ltrim(substr($val,0,32))
//                ]);
//                $i++;
//                print_r($new_data);exit;
//            }else{
//
//                array_unshift($new_data,$title);
//                $cvs_data = $new_data;
//                $new_data = [];
//                $i = 1;
//                array_push($new_data,[
//                    'md5' => substr($val,0,32)
//                ]);
//                ExcelService::exportExcel($cvs_data,'/var/www/phone_md5/newjidai/data_'.$key.'.csv',true);
//                $this->stdout( $key ." success\n");
//            }
//
//        }
//        ExcelService::exportExcel($new_data,'/var/www/phone_md5/newjidai/data_'.$i.'.csv',true);
//
//        exit;


        //2.0版本
        $new_data = [];
        foreach($data as $key=>$value){
            if($key == 0){
                continue;
            }
            $value = $value[0];
            $table_name = substr($value,0,2);
            $table_name = 'phone_' .$table_name;

            if(!array_key_exists($table_name,$new_data)){
                $new_data[$table_name] = [];
            }
            $new_data[$table_name][]= $value;
        }
        $new_array = [];
        foreach ($new_data as $key=>$val){
            $table_name = $key;
            $phones = (new Query())
                ->from($table_name)
                ->where(['phone_md5'=>$val])
                ->all();
            if($phones){
                $new_array = array_merge($new_array,$phones);
            }

        }
        //1.0版本
//        $new_array = [];
//        foreach ($data as $key=>$value)
//        {
//            $value = $value[0];
//            $table_name = $value[0] . $value[1];
//            $sql = "select * from phone_". $table_name ." where phone_md5 = '$value'";
//            $row = Phone::getDb()->createCommand($sql)
//                ->query()->read();
//            if(!$row){
//                $row = [
//                    'id' => '',
//                    'phone'=>'',
//                    'phone_md5'=>$value
//                ];
//            }
//            array_push($new_array,$row);
//        }


        // 保存数据. xlsx格式
//        ExcelService::exportExcel($new_array,'/var/www/phone_md5/data.xlsx',true);
        // 保存数据 csv格式
        ExcelService::exportExcel($data,'/var/www/phone_md5/data.csv',true);

    }

    public function actionImportTb()
    {
        //xlsx 格式
//        $reader = IOFactory::createReader('Xlsx');
        //csv 格式
        $a = [0,1,2,3,4,5,6,7,8,9,'a','b','c','d','e','f'];
        foreach ($a as $value){
            foreach ($a as $item){
                $table_name = 'phone_'.$value.$item;
                $reader = IOFactory::createReader('Csv');
                $spreat_sheet = $reader->load('/var/www/phone_md5/newjidai_1/data_'.$table_name.'.csv');
                $data = $spreat_sheet->getActiveSheet()->toArray();
                $arr = [];
                foreach ($data as $i){
                    $arr[] = ltrim($i[0]);

                }
                $phones = (new Query())
                    ->from($table_name)
                    ->where(['phone_md5'=>$arr])
                    ->all();
                if(!empty($phones)){
                    ExcelService::exportExcel($phones,'/var/www/phone_md5/new_data_1/data_'.$table_name.'.csv',true);
                }
                $this->stdout( $table_name ." success\n");

            }
        }
        $this->stdout( $table_name ." ok\n");
        exit;
    }


}